{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:14.532377Z",
     "start_time": "2025-06-29T09:00:14.526808Z"
    }
   },
   "source": "import pandas as pd",
   "outputs": [],
   "execution_count": 19
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:14.845826Z",
     "start_time": "2025-06-29T09:00:14.843072Z"
    }
   },
   "cell_type": "code",
   "source": "import numpy as np",
   "id": "c410905d0a5ae345",
   "outputs": [],
   "execution_count": 20
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.041905Z",
     "start_time": "2025-06-29T09:00:14.990447Z"
    }
   },
   "cell_type": "code",
   "source": "df = pd.read_excel(\"example.xlsx\", engine=\"openpyxl\")",
   "id": "26709e5678a3483d",
   "outputs": [],
   "execution_count": 21
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.144243Z",
     "start_time": "2025-06-29T09:00:15.138863Z"
    }
   },
   "cell_type": "code",
   "source": "df",
   "id": "105d3288f4ac79b9",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Gene     logFC        PValue\n",
       "0        Gene1 -5.444561  8.386036e-07\n",
       "1        Gene2 -5.649062  1.463526e-06\n",
       "2        Gene3 -5.934843  7.604120e-06\n",
       "3        Gene4 -4.309913  8.621032e-06\n",
       "4        Gene5 -4.166778  1.047254e-05\n",
       "...        ...       ...           ...\n",
       "1664  Gene1665 -0.015591  1.000000e+00\n",
       "1665  Gene1666 -0.009071  1.000000e+00\n",
       "1666  Gene1667  0.008679  1.000000e+00\n",
       "1667  Gene1668 -0.007746  1.000000e+00\n",
       "1668  Gene1669  0.007112  1.000000e+00\n",
       "\n",
       "[1669 rows x 3 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gene</th>\n",
       "      <th>logFC</th>\n",
       "      <th>PValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gene1</td>\n",
       "      <td>-5.444561</td>\n",
       "      <td>8.386036e-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gene2</td>\n",
       "      <td>-5.649062</td>\n",
       "      <td>1.463526e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gene3</td>\n",
       "      <td>-5.934843</td>\n",
       "      <td>7.604120e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gene4</td>\n",
       "      <td>-4.309913</td>\n",
       "      <td>8.621032e-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gene5</td>\n",
       "      <td>-4.166778</td>\n",
       "      <td>1.047254e-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1664</th>\n",
       "      <td>Gene1665</td>\n",
       "      <td>-0.015591</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1665</th>\n",
       "      <td>Gene1666</td>\n",
       "      <td>-0.009071</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1666</th>\n",
       "      <td>Gene1667</td>\n",
       "      <td>0.008679</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1667</th>\n",
       "      <td>Gene1668</td>\n",
       "      <td>-0.007746</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1668</th>\n",
       "      <td>Gene1669</td>\n",
       "      <td>0.007112</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1669 rows × 3 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.291584Z",
     "start_time": "2025-06-29T09:00:15.289187Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"abs_logFC\"] = np.absolute(df[\"logFC\"])",
   "id": "f660500b74caf6f5",
   "outputs": [],
   "execution_count": 23
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.443895Z",
     "start_time": "2025-06-29T09:00:15.438654Z"
    }
   },
   "cell_type": "code",
   "source": "df",
   "id": "24085b87a529e1aa",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Gene     logFC        PValue  abs_logFC\n",
       "0        Gene1 -5.444561  8.386036e-07   5.444561\n",
       "1        Gene2 -5.649062  1.463526e-06   5.649062\n",
       "2        Gene3 -5.934843  7.604120e-06   5.934843\n",
       "3        Gene4 -4.309913  8.621032e-06   4.309913\n",
       "4        Gene5 -4.166778  1.047254e-05   4.166778\n",
       "...        ...       ...           ...        ...\n",
       "1664  Gene1665 -0.015591  1.000000e+00   0.015591\n",
       "1665  Gene1666 -0.009071  1.000000e+00   0.009071\n",
       "1666  Gene1667  0.008679  1.000000e+00   0.008679\n",
       "1667  Gene1668 -0.007746  1.000000e+00   0.007746\n",
       "1668  Gene1669  0.007112  1.000000e+00   0.007112\n",
       "\n",
       "[1669 rows x 4 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gene</th>\n",
       "      <th>logFC</th>\n",
       "      <th>PValue</th>\n",
       "      <th>abs_logFC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gene1</td>\n",
       "      <td>-5.444561</td>\n",
       "      <td>8.386036e-07</td>\n",
       "      <td>5.444561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gene2</td>\n",
       "      <td>-5.649062</td>\n",
       "      <td>1.463526e-06</td>\n",
       "      <td>5.649062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gene3</td>\n",
       "      <td>-5.934843</td>\n",
       "      <td>7.604120e-06</td>\n",
       "      <td>5.934843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gene4</td>\n",
       "      <td>-4.309913</td>\n",
       "      <td>8.621032e-06</td>\n",
       "      <td>4.309913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gene5</td>\n",
       "      <td>-4.166778</td>\n",
       "      <td>1.047254e-05</td>\n",
       "      <td>4.166778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1664</th>\n",
       "      <td>Gene1665</td>\n",
       "      <td>-0.015591</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.015591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1665</th>\n",
       "      <td>Gene1666</td>\n",
       "      <td>-0.009071</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.009071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1666</th>\n",
       "      <td>Gene1667</td>\n",
       "      <td>0.008679</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.008679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1667</th>\n",
       "      <td>Gene1668</td>\n",
       "      <td>-0.007746</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.007746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1668</th>\n",
       "      <td>Gene1669</td>\n",
       "      <td>0.007112</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.007112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1669 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 24
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.555604Z",
     "start_time": "2025-06-29T09:00:15.553099Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"neg_log10PValue\"] = -np.log10(df[\"PValue\"])",
   "id": "27235639386ee3a4",
   "outputs": [],
   "execution_count": 25
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.714840Z",
     "start_time": "2025-06-29T09:00:15.709179Z"
    }
   },
   "cell_type": "code",
   "source": "df",
   "id": "b4c94b340a52f923",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Gene     logFC        PValue  abs_logFC  neg_log10PValue\n",
       "0        Gene1 -5.444561  8.386036e-07   5.444561         6.076443\n",
       "1        Gene2 -5.649062  1.463526e-06   5.649062         5.834600\n",
       "2        Gene3 -5.934843  7.604120e-06   5.934843         5.118951\n",
       "3        Gene4 -4.309913  8.621032e-06   4.309913         5.064441\n",
       "4        Gene5 -4.166778  1.047254e-05   4.166778         4.979948\n",
       "...        ...       ...           ...        ...              ...\n",
       "1664  Gene1665 -0.015591  1.000000e+00   0.015591        -0.000000\n",
       "1665  Gene1666 -0.009071  1.000000e+00   0.009071        -0.000000\n",
       "1666  Gene1667  0.008679  1.000000e+00   0.008679        -0.000000\n",
       "1667  Gene1668 -0.007746  1.000000e+00   0.007746        -0.000000\n",
       "1668  Gene1669  0.007112  1.000000e+00   0.007112        -0.000000\n",
       "\n",
       "[1669 rows x 5 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gene</th>\n",
       "      <th>logFC</th>\n",
       "      <th>PValue</th>\n",
       "      <th>abs_logFC</th>\n",
       "      <th>neg_log10PValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gene1</td>\n",
       "      <td>-5.444561</td>\n",
       "      <td>8.386036e-07</td>\n",
       "      <td>5.444561</td>\n",
       "      <td>6.076443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gene2</td>\n",
       "      <td>-5.649062</td>\n",
       "      <td>1.463526e-06</td>\n",
       "      <td>5.649062</td>\n",
       "      <td>5.834600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Gene3</td>\n",
       "      <td>-5.934843</td>\n",
       "      <td>7.604120e-06</td>\n",
       "      <td>5.934843</td>\n",
       "      <td>5.118951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gene4</td>\n",
       "      <td>-4.309913</td>\n",
       "      <td>8.621032e-06</td>\n",
       "      <td>4.309913</td>\n",
       "      <td>5.064441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Gene5</td>\n",
       "      <td>-4.166778</td>\n",
       "      <td>1.047254e-05</td>\n",
       "      <td>4.166778</td>\n",
       "      <td>4.979948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1664</th>\n",
       "      <td>Gene1665</td>\n",
       "      <td>-0.015591</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.015591</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1665</th>\n",
       "      <td>Gene1666</td>\n",
       "      <td>-0.009071</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.009071</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1666</th>\n",
       "      <td>Gene1667</td>\n",
       "      <td>0.008679</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.008679</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1667</th>\n",
       "      <td>Gene1668</td>\n",
       "      <td>-0.007746</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.007746</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1668</th>\n",
       "      <td>Gene1669</td>\n",
       "      <td>0.007112</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.007112</td>\n",
       "      <td>-0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1669 rows × 5 columns</p>\n",
       "</div>"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 26
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.891138Z",
     "start_time": "2025-06-29T09:00:15.888282Z"
    }
   },
   "cell_type": "code",
   "source": "df[\"Rank\"] = df[\"logFC\"].rank(method=\"first\", ascending=False).astype(int)",
   "id": "81ef14080c37e041",
   "outputs": [],
   "execution_count": 27
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:15.981787Z",
     "start_time": "2025-06-29T09:00:15.978561Z"
    }
   },
   "cell_type": "code",
   "source": [
    "def mark_top_5_or_all_F(group):\n",
    "    group[\"Regular\"] = group.name\n",
    "    if group.name == 'not':\n",
    "        # 分组名是 'not'，全部标记为 'F'\n",
    "        group['flag'] = 'F'\n",
    "    else:\n",
    "        # 正常分组，标记 logFC_abs 最大的5个为 'T'，其他为 'F'\n",
    "        top_5 = group.nlargest(5, 'abs_logFC')\n",
    "        group['flag'] = group['Gene'].isin(top_5['Gene']).map({True: 'T', False: 'F'})\n",
    "        print(group)\n",
    "    return group\n",
    "\n",
    "def func(x):\n",
    "    if x[\"PValue\"] >= 0.05:\n",
    "        return \"not\"\n",
    "    if x[\"logFC\"] > 1 :\n",
    "        return \"up\"\n",
    "    if x[\"logFC\"] < -1:\n",
    "        return \"down\"\n",
    "    return \"not\"\n",
    "    \n",
    "\n"
   ],
   "id": "78f1d10efbfc1593",
   "outputs": [],
   "execution_count": 28
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:16.145352Z",
     "start_time": "2025-06-29T09:00:16.129824Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df[\"Regular\"] = df.apply(func, axis=1)\n",
    "df = df.groupby('Regular',as_index=False).apply(mark_top_5_or_all_F, include_groups=False)"
   ],
   "id": "1e4bc2c9d44aee25",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Gene     logFC        PValue  abs_logFC  neg_log10PValue  Rank  \\\n",
      "0      Gene1 -5.444561  8.386036e-07   5.444561         6.076443  1662   \n",
      "1      Gene2 -5.649062  1.463526e-06   5.649062         5.834600  1663   \n",
      "2      Gene3 -5.934843  7.604120e-06   5.934843         5.118951  1664   \n",
      "3      Gene4 -4.309913  8.621032e-06   4.309913         5.064441  1649   \n",
      "4      Gene5 -4.166778  1.047254e-05   4.166778         4.979948  1646   \n",
      "..       ...       ...           ...        ...              ...   ...   \n",
      "200  Gene201 -1.234992  4.846484e-02   1.234992         1.314573  1350   \n",
      "202  Gene203 -2.348426  4.866604e-02   2.348426         1.312774  1557   \n",
      "203  Gene204 -1.299214  4.871354e-02   1.299214         1.312350  1370   \n",
      "204  Gene205 -2.482903  4.944443e-02   2.482903         1.305883  1564   \n",
      "205  Gene206 -1.352392  4.967943e-02   1.352392         1.303823  1384   \n",
      "\n",
      "    Regular flag  \n",
      "0      down    F  \n",
      "1      down    F  \n",
      "2      down    F  \n",
      "3      down    F  \n",
      "4      down    F  \n",
      "..      ...  ...  \n",
      "200    down    F  \n",
      "202    down    F  \n",
      "203    down    F  \n",
      "204    down    F  \n",
      "205    down    F  \n",
      "\n",
      "[142 rows x 8 columns]\n",
      "        Gene     logFC    PValue  abs_logFC  neg_log10PValue  Rank Regular  \\\n",
      "9     Gene10  3.172001  0.000063   3.172001         4.198864    14      up   \n",
      "12    Gene13  3.914482  0.000120   3.914482         3.922336     4      up   \n",
      "16    Gene17  4.407907  0.000195   4.407907         3.710910     2      up   \n",
      "20    Gene21  3.026456  0.000436   3.026456         3.360233    16      up   \n",
      "38    Gene39  2.585272  0.002011   2.585272         2.696580    36      up   \n",
      "..       ...       ...       ...        ...              ...   ...     ...   \n",
      "186  Gene187  1.775998  0.043952   1.775998         1.357024    97      up   \n",
      "194  Gene195  2.214651  0.046933   2.214651         1.328526    59      up   \n",
      "198  Gene199  1.620426  0.047815   1.620426         1.320433   137      up   \n",
      "199  Gene200  1.977304  0.048153   1.977304         1.317377    68      up   \n",
      "201  Gene202  1.365912  0.048569   1.365912         1.313643   175      up   \n",
      "\n",
      "    flag  \n",
      "9      T  \n",
      "12     T  \n",
      "16     T  \n",
      "20     T  \n",
      "38     F  \n",
      "..   ...  \n",
      "186    F  \n",
      "194    F  \n",
      "198    F  \n",
      "199    F  \n",
      "201    F  \n",
      "\n",
      "[64 rows x 8 columns]\n"
     ]
    }
   ],
   "execution_count": 29
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-06-29T09:00:16.546462Z",
     "start_time": "2025-06-29T09:00:16.536472Z"
    }
   },
   "cell_type": "code",
   "source": "df.to_csv(\"example.csv\", header=True, index=False, encoding=\"utf-8\")",
   "id": "c1f5271dd15546df",
   "outputs": [],
   "execution_count": 30
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "ead802b471f2188f"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
